package com.pub.database.access;

import java.util.Hashtable;
import com.pub.database.DBFunction;

public class AccessFunction {
    public AccessFunction() {
    }
    
    public static String incPageSizeSQL(String pstrSQL,long plngStartRow,long plngRowCount) throws Exception{

    	DBFunction dbFunction = null;
    	Hashtable<String,String> lhtSqlInfo = null;
    	
    	try{
    		dbFunction = new DBFunction();
    		
    		lhtSqlInfo = dbFunction.splitSQL(pstrSQL);
    		
    		return incPageSizeSQL(
    				lhtSqlInfo.get("FieldsList").toString(),
    				lhtSqlInfo.get("TablesList").toString(),
    				lhtSqlInfo.get("pstrSQLCondition").toString(),
    				lhtSqlInfo.get("pstrGroupBy").toString(),
    				lhtSqlInfo.get("pstrHaving").toString(),
    				lhtSqlInfo.get("pstrOrderByFields").toString(),
    				lhtSqlInfo.get("pstrOrderBySeq").toString(),
    				plngStartRow,
    				plngRowCount);
    	}
    	catch(Exception e){
    		throw new Exception(e);
    	}
    }

    public static String incPageSizeSQL(String pstrFieldsList,
                                        String pstrTablesList,
                                        String pstrSQLCondition,
                                        String pstrGroupBy,
                                        String pstrHaving,
                                        String pstrOrderByFields,
                                        String pstrOrderBySeq,
                                        long plngStartRow,
                                        long plngPageRowCount) {
        StringBuffer lstbPageSQL = new StringBuffer();

        lstbPageSQL.append("select * from (select");
        lstbPageSQL.append(" top ");
        lstbPageSQL.append(plngPageRowCount);
        lstbPageSQL.append(" * from (select top ");
        lstbPageSQL.append(plngStartRow);
        lstbPageSQL.append(" * from (select ");
        lstbPageSQL.append(pstrFieldsList);
        lstbPageSQL.append(" from ");
        lstbPageSQL.append(pstrTablesList);
        lstbPageSQL.append(pstrSQLCondition!=null && !pstrSQLCondition.trim().equals("") ? " where " + pstrSQLCondition : "");
        lstbPageSQL.append(pstrGroupBy!=null && !pstrGroupBy.trim().equals("") ? " group by " + pstrGroupBy : "");
        lstbPageSQL.append(pstrHaving!=null && !pstrHaving.trim().equals("") ? " having " + pstrHaving : "");
        lstbPageSQL.append(pstrOrderByFields!=null && !pstrOrderByFields.trim().equals("") ? " order by " + pstrOrderByFields + " " : "");
        lstbPageSQL.append(pstrOrderBySeq!=null && !pstrOrderBySeq.trim().equals("")? pstrOrderBySeq : "");
        lstbPageSQL.append("))");
        lstbPageSQL.append(pstrOrderByFields!=null && !pstrOrderByFields.trim().equals("") ? " order by " + pstrOrderByFields + " desc" : "");
        lstbPageSQL.append(")");
        lstbPageSQL.append(pstrOrderByFields!=null && !pstrOrderByFields.trim().equals("") ? " order by " + pstrOrderByFields + " " : "");
        lstbPageSQL.append(pstrOrderBySeq!=null && !pstrOrderBySeq.trim().equals("")? pstrOrderBySeq : "");

        return lstbPageSQL.toString();
    }

    public static String incPageSizeSQLForDV(String[][] pstrFieldsList,
                                             int pintFieldDataIndex,
                                             int pintNowPage,
                                             long plngPageRowCount,
                                             long plngTotalDataSize,
                                             String pstrTablesList,
                                             String pstrSQLCondition,
                                             String pstrOrderByFields,
                                             String pstrOrderBySeq,
                                             String pstrDVSQLCondition,
                                             String pstrDVOrderByFields,
                                             String pstrDVOrderBySeq) {
        StringBuffer lstbPageSQL = new StringBuffer();
        //String lstrSQLCondition=null;
        String lstrOrderByFields = null;
        String lstrOrderBySeq = null;

        lstrOrderByFields = (pstrDVOrderByFields == null ||
                             pstrDVOrderByFields.trim().equals("") ||
                             pstrDVOrderByFields.trim().equals("null") ?
                             pstrOrderByFields : pstrDVOrderByFields);
        lstrOrderBySeq = (pstrDVOrderBySeq == null ||
                          pstrDVOrderBySeq.trim().equals("") ? pstrOrderBySeq :
                          pstrDVOrderBySeq);

        lstbPageSQL.append("select * from (select top ");
        lstbPageSQL.append((plngTotalDataSize/plngPageRowCount) + 1 != pintNowPage ? plngPageRowCount : plngTotalDataSize%plngPageRowCount);
        lstbPageSQL.append(" * from (select top ");
        lstbPageSQL.append(plngPageRowCount * pintNowPage);
        lstbPageSQL.append(" * from (select * from (select ");
        for(int i=0;i<pstrFieldsList.length;i++){
            lstbPageSQL.append(pstrFieldsList[i][pintFieldDataIndex]);
            if(!(i==pstrFieldsList.length-1)){
                lstbPageSQL.append(",");
            }
        }
        lstbPageSQL.append(" from ");
        lstbPageSQL.append(pstrTablesList);
        if (pstrSQLCondition != null && !pstrSQLCondition.trim().equals("")) {
            lstbPageSQL.append(" where ");
            lstbPageSQL.append(pstrSQLCondition);
        }
        lstbPageSQL.append(" )");
        if (pstrOrderByFields != null && !pstrOrderByFields.trim().equals("")) {
            lstbPageSQL.append(" order by ");
            lstbPageSQL.append(lstrOrderByFields);
            lstbPageSQL.append(" ");
            lstbPageSQL.append(lstrOrderBySeq);
        }
        lstbPageSQL.append("))");
        lstbPageSQL.append(lstrOrderByFields == null ||
                           lstrOrderByFields.equals("") ? "" :
                           " order by " + lstrOrderByFields + " desc");
        lstbPageSQL.append(")");
        lstbPageSQL.append(lstrOrderByFields == null ||
                           lstrOrderByFields.equals("") ? "" :
                           " order by " + lstrOrderByFields + " " +
                           lstrOrderBySeq);

        return lstbPageSQL.toString();
    }
}
